package org.itec.yeast.db;

import java.lang.reflect.Constructor;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.itec.yeast.util.BeanUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 封装数据库操作，所有的JDBC操作和数据库操作都在此类中进行，其他类通过调用本类的方法获取数据。
 * 
 * @author GGCoke
 *
 */
public class DataManager {
	private static Logger log = LoggerFactory.getLogger(DataManager.class);
	private static DBConnectionPool pool = DBConnectionPool.getInstance();
	
	/**
	 * 查询操作
	 * @param sql
	 * 			进行查询的SQL语句
	 * @return
	 * 			返回以List形式存储的结果。List中的每一项都是一个Map对象，Map的key是列的名称，value是列的值
	 */
	public static List executeQuery(final String sql){
		List result = new ArrayList();
		if (pool == null){
			pool = DBConnectionPool.getInstance();
		}
		
		Connection conn = null;
		Statement stmt  = null;
		ResultSet rs    = null;
		
		try {
			conn = pool.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs == null){
				return null;
			}

			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next()){
				Map map = new HashMap();
				for (int i = 1; i <= rsmd.getColumnCount(); i++){
					map.put(rsmd.getColumnName(i), rs.getObject(i));
				}
				result.add(map);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			log.error("数据库查询错误!", e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return result;
	}

	/**
	 * 查询操作
	 * @param sql
	 * 			进行查询的SQL语句
	 * @param params
	 * 			查询语句中的参数
	 * @return
	 * 			返回以List形式存储的结果。List中的每一项都是一个Map对象，Map的key是列的名称，value是列的值
	 */
	public static List executeQuery(final String sql, final Object[] params){
		List result = new ArrayList();
		if (pool == null){
			pool = DBConnectionPool.getInstance();
		}
		
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs    = null;
		
		try {
			conn = pool.getConnection();
			pst = conn.prepareStatement(sql);
			
			// 设置参数
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					pst.setObject(i + 1, params[i]);
				}
			}
			
			rs = pst.executeQuery();
			if (rs == null){
				return null;
			}
			
			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next()){
				Map map = new HashMap();
				for (int i = 1; i <= rsmd.getColumnCount(); i++){
					map.put(rsmd.getColumnName(i), rs.getObject(i));
				}
				result.add(map);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			log.error("数据库查询错误!", e);
		} finally {
			closeJDBCResources(rs, pst, conn);
		}
		
		return result;
	}
	
	/**
	 * 更新操作
	 * @param sql
	 * 			进行更新的SQL语句
	 * @return
	 * 			(1) 更新涉及的行数 <br />
	 * 			(2) 0代表没有数据进行更新
	 */
	public static int executeUpdate(final String sql){
		int result = -1;
		Connection conn = null;
		Statement stmt  = null;
		if (pool == null){
			pool = DBConnectionPool.getInstance();
		}
		
		try {
			conn = pool.getConnection();
			stmt = conn.createStatement();
			result = stmt.executeUpdate(sql);
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return result;
	}
	
	/**
	 * 更新操作
	 * @param sql
	 * 			进行更新的SQL语句
	 * @param params
	 * 			更新SQL语句中的参数
	 * @return
	 * 			(1) 更新涉及的行数 <br />
	 * 			(2) 0代表没有数据进行更新
	 */
	public static int executeUpdate(final String sql, final Object[] params){
		int result = -1;
		Connection conn = null;
		PreparedStatement pst = null;
		if (pool == null){
			pool = DBConnectionPool.getInstance();
		}
		
		try {
			conn = pool.getConnection();
			pst  = conn.prepareStatement(sql);
			
			// 设置参数
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					pst.setObject(i + 1, params[i]);
				}
			}
			
			result = pst.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return result;
	}
	
	/**
	 * 批量插入。<br />
	 * 批量插入时要注意设置两点：<br />
	 * 1. jdbc_url中要设置rewriteBatchedStatements=true <br />
	 * 2. 连接要设置AutoCommit为false，即conn.setAutoCommit(false);
	 * @param sql
	 * 			进行插入操作的SQL语句
	 * @param list
	 * 			参数的list
	 * @return
	 * 			插入的记录条数
	 */
	public static int executeBatchInsert(final String sql, List<Object[]> list){
		Connection conn = null;
		PreparedStatement pst = null;
		int result = -1;
		try {
			conn = pool.getConnection();
			
			conn.setAutoCommit(false);
			pst = conn.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++){
				Object[] params = list.get(i);
				for (int j = 0; j < params.length; j++){
					pst.setObject(j+1, params[j]);
				}
				
				pst.addBatch();
			}
			
			int[] commitResult = pst.executeBatch();
			result = commitResult.length;
			conn.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.error(e.getMessage());
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return result;
	}
	
	public static boolean isExists(final String sql, Object[] params){
		boolean result = false;
		if (pool == null){
			pool = DBConnectionPool.getInstance();
		}
		
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs    = null;
		
		try {
			conn = pool.getConnection();
			pst = conn.prepareStatement(sql);
			
			// 设置参数
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					pst.setObject(i + 1, params[i]);
				}
			}
			
			rs = pst.executeQuery();
			if (rs.next()){
				result = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			log.error("数据库查询错误!", e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return result;
	}
	
	public static <T> List<T> queryBySql(final Class<T> clazz, final String conditionSql,
			final Object[] params) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		ResultSetMetaData meta = null;
		int columnLength = 0;
		List<T> result = new ArrayList<T>();
		try {
			conn = pool.getConnection();
			pst = conn.prepareStatement(conditionSql);
			if (params != null && params.length > 0) {
				// 设置参数
				for (int i = 0; i < params.length; i++) {
					pst.setObject(i + 1, params[i]);
				}
			}
			rs = pst.executeQuery();
			meta = rs.getMetaData();
			columnLength = meta.getColumnCount();
			
			String[] columsTypes = new String[columnLength];
			for (int i = 0; i < columnLength; i ++) {
				columsTypes[i] = meta.getColumnClassName(i + 1);
			}
			Constructor<T> constructor = BeanUtils.getConstructor(clazz, columsTypes);
			Object[] columns = new Object[columnLength];
			while (rs.next()) {
				for (int i = 0; i < columnLength; i++) {
					columns[i] = rs.getObject(i + 1);
				}
				result.add(constructor.newInstance(columns));
			}
		} catch (Exception e) {
			log.error("数据库查询出现异常。", e);
		} finally {
			closeJDBCResources(rs, pst, conn);
		}
		return result;
	}
	
	
	/**
	 * 关闭相关的JDBC资源
	 * 
	 * @param rs
	 * @param st
	 * @param conn
	 * @throws SQLException
	 */
	private static void closeJDBCResources(ResultSet rs, Statement st,
			Connection conn) {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (st != null) {
				st.close();
				st = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (Exception e) {
			log.error("关闭JDBC资源时出现异常：", e);
		}
	}
	public static void main(String [] args){
		String sql = "select idGENE, Feature_Name from gene";
		List list = DataManager.executeQuery(sql);
	}
}
